﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Model.Ent;
using OMG.Fill.Vegetables.Service;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Vegetable.Service
{
    /// <summary>
    /// 填报项目设置T_Set_List通用操作类
    /// </summary>
    public class SetListServices:SetLists
    {
        public Pager<List<SetListModel>> GetPageList(SetListQuery query)
        {
            Pager<List<SetListModel>> obj = new Pager<List<SetListModel>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.TableCode))
            {
                strWhereSql += "and TableCode = @TableCode ";
                paramlist.Add("TableCode", query.TableCode);
            }
            if (!string.IsNullOrEmpty(query.ParentCode))
            {
                strWhereSql += "and ParentCode = @ParentCode ";
                paramlist.Add("ParentCode", query.ParentCode);
            }
            if (!string.IsNullOrEmpty(query.Status))
            {
                strWhereSql += "and Status = @Status ";
                paramlist.Add("Status", query.Status);
            }
            if (!string.IsNullOrEmpty(query.ItemCode))
            {
                strWhereSql += "and ItemCode like @ItemCode ";
                paramlist.Add("ItemCode", "%" + query.ItemCode + "%");
            }
            if (!string.IsNullOrEmpty(query.ItemName))
            {
                strWhereSql += "and ItemName like @ItemName ";
                paramlist.Add("ItemName", "%" + query.ItemName + "%");
            }
            string strFromSql = "from dbo.T_Set_List where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = " order by TableCode asc,ParentCode asc,ItemCode asc ";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<SetListModel>(strSql,paramlist).ToList();
                    foreach (var item in obj.rows)
                    {
                        item.StatusName = DataDict.Instance["StatusList"][item.Status];
                        item.TableName = DataDict.Instance["ReportTableName"][item.TableCode];
                    }
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取填报项目设置T_Set_List分页列表失败", ex);
                return obj;
            }
        }


        /// <summary>
        /// 获取表集合
        /// </summary>
        /// <param name="tableCode"></param>
        /// <returns></returns>
        public virtual ResResult<List<SortSetItem>> GetSearchList(string tableCode)
        {
            ResResult<List<SortSetItem>> obj = new ResResult<List<SortSetItem>>();
            var paramlist = new DynamicParameters();
            string strSql = @"select ParentCode,ItemName,ItemCode from dbo.T_Set_List where TableCode = @TableCode ";
            paramlist.Add("TableCode", tableCode);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<SortSetItem>(strSql, paramlist).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取失败", ex);
                return obj;
            }
        }
        public ResResult<List<SetListEntity>> getListAll(string TableCode)
        {
            ResResult<List<SetListEntity>> list = new ResResult<List<SetListEntity>>();
            if (!string.IsNullOrEmpty(TableCode))
            {
                var paramlist = new DynamicParameters();
                paramlist.Add("TableCode", TableCode);
                string strSql = "select * from dbo.T_Set_List where TableCode=@TableCode";
                try
                {
                    list.message = "获取表格分类节点成功";
                    list.model = dbCon.Query<SetListEntity>(strSql,paramlist).ToList(); 
                    list.status = DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    list.message = "获取表格分类节点异常,异常信息：" + ex.ToString().Trim();
                    list.status = DefaultKey.ErrorResult;
                }

            }
            else
            {
                list.message = "系统逻辑错误，请与管理员联系";
                list.status = DefaultKey.ErrorResult;
            }
            return list;
        }
        public ResResult<List<SetListEntity>> GetSortList(string TableCode)
        {
            ResResult<List<SetListEntity>> result = new ResResult<List<SetListEntity>>();
            if (!string.IsNullOrEmpty(TableCode))
            {
                var paramlist = new DynamicParameters();
                paramlist.Add("TableCode", TableCode);
                //根据报表ID获取报表二级节点信息
                string sqlSetList = "select * from [dbo].[T_Set_List] where TableCode=@TableCode  and parentCode = @TableCode";
                // string sqlVegProsecutionStr = "select distinct SortCode,SortName,ReportId  from [dbo].[T_VegProsecution] where  ReportId='" + ReportModel.Id + "'";
                result.model = dbCon.Query<SetListEntity>(sqlSetList, paramlist).ToList();
                if (result.model.Count != 0)
                {
                    result.message = "获取蔬菜生产实体调查表二级节点信息成功";
                    result.status = DefaultKey.SuccessResult;
                }
                else
                {
                    result.message = "获取蔬菜生产实体调查表二级节点信息成功,信息不存在";
                    result.status = DefaultKey.InfoResult;
                }
            }
            else
            {
                result.message = "对应的表不存在,请稍后再试";
                result.status = DefaultKey.ErrorResult;
                LogHelper.WriteLog("获取二级项目信息失败,地区代码为空");
            }
            return result;
        }
        /// <summary>
        /// 将setList的Excel数据导入到数据表dbo.setList库中(批量添加)
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public ResResult<SetListEntity> ExportExcel(string filePath)
        {
            ResResult<SetListEntity> list = new ResResult<SetListEntity>();

            if (!string.IsNullOrEmpty(filePath))
            {
                try
                {
                    SetListEntity model;
                    IWorkbook workbook = null;  //新建IWorkbook对象  
                    FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                    if (filePath.IndexOf(".xlsx") > 0) // 2007版本  
                    {
                        workbook = new XSSFWorkbook(fileStream);  //xlsx数据读入workbook  
                    }
                    else if (filePath.IndexOf(".xls") > 0) // 2003版本  
                    {
                        workbook = new HSSFWorkbook(fileStream);  //xls数据读入workbook  
                    }
                    ISheet sheet = workbook.GetSheetAt(0);  //获取第一个工作表  
                    IRow row;// = sheet.GetRow(0);            //新建当前工作表行数据  
                    for (int i = 1; i < sheet.LastRowNum; i++)  //对工作表每一行  
                    {
                        row = sheet.GetRow(i);   //row读入第i行数据  
                        if (row != null)
                        {
                            model = new SetListEntity();
                            for (int j = 0; j < row.LastCellNum; j++)  //对工作表每一列  
                            {

                                if (row.GetCell(j) != null)
                                {//设置 
                                    string CellValue = row.GetCell(j).ToString().Trim();
                                    model.Id = CustomFun.GetGuid();
                                    model.Status = "1";//分类状态
                                    if (j == 0)
                                    {//所属表格代码
                                        model.TableCode = CellValue;
                                    }
                                    else if (j == 2)
                                    {//父分类代码
                                        model.ParentCode = CellValue;
                                    }
                                    else if (j == 4)
                                    {//分类代码
                                        model.ItemCode = CellValue;
                                    }
                                    else if (j == 5)
                                    {//分类名称
                                        model.ItemName = CellValue;
                                    }
                                    else if (j == 6)
                                    {//单位
                                        model.UnitName = CellValue;
                                    }
                                    else if (j == 7)
                                    {
                                        model.Sort = CellValue;
                                    }
                                    else if (j == 8)
                                    {
                                        model.Remark = CellValue;
                                    }
                                }
                            }
                            AddInfo(model);//添加一列数据到setList
                        }
                    }
                    fileStream.Close();
                    fileStream.Dispose();
                    workbook.Close();
                    workbook.Dispose();
                }
                catch (Exception ex)
                {
                    list.message = "数据导入失败,请稍后再试。错误信息:" + ex.ToString().Trim();
                    list.status = DefaultKey.ErrorResult;
                }
            }
            else
            {
                list.message = "数据导入失败，找不到文件名为:" + filePath.ToString().Trim() + "的文件";
                list.status = DefaultKey.ErrorResult;
            }
            return list;
        }
    }
}